<?php

namespace User\Controller;

use Common\Controller\AdminbaseController;

class IndexlinksController extends AdminbaseController
{
    private $user_model;
    private $model;

    public function __construct()
    {
        parent::__construct();
        $this->model = M( "distribution", 'system_', C( 'DB_CONFIG2' ) );
        //$this->link_model=M("link",'system_',C('DB_CONFIG2'));
        $this->user_model = M( "user", 'system_', C( 'DB_CONFIG2' ) );
    }
    /*
     * 我的分销 用户列表
     */
    public function links_my_user(){
        $where=array(
            "a.`code` !=''",
        );
        $request=I('request.');
        if(!empty($request['name'])){
            $where[] = "a.name like '%{$request['name']}%'";
        }
        if(!empty($request['starts'])){
            $where[] = "a.crtime>='{$request['starts']}''";
        }
        if(!empty($request['ends'])){
            $where[] = "a.crtime<='{$request['ends']}''";
        }
        $user_id=get_current_admin_id();
        if(get_groups_admin_user()==8 || get_groups_admin_user()==14 || get_groups_admin_user()==10 || get_groups_admin_user()==6 || $user_id==1){
            $wheres=array();
        }else{
            $wheres=array(array('user_id'=>$user_id));
        }
        $codes=$this->model->where($wheres)->where(array('is_dis'=>0))->field('id,code,crtime,name,user_id')->order('id asc')->select();
        $code=array();
        $type=I('request.id',1);
        $i=1;
        foreach($codes as $v){
            if($type==$i) {
                $code[] = "'" . $v['code'] . "'";
                break;
            }
            $i++;
        }
        if(!empty($code)){
            $where[]="a.`code` in (".implode(',',$code).")";
            $sql2='select count(a.id) as num from system_user a WHERE '.implode(' and ',$where);
            $count=$this->user_model->query($sql2);
            $page = $this->page($count[0]['num'], 20);
            $sql='SELECT a.`name`,a.`crtime`,a.`mobile`,a.`identity`,b.amount,b.`status` FROM system_user a ,(SELECT amount,status,user_id from orders WHERE id in (SELECT MAX(id) FROM orders WHERE renew=0 GROUP BY user_id)) b WHERE '.implode(' and ',$where).' AND b.user_id=a.id GROUP BY a.id order by crtime desc limit '.$page->firstRow . ',' . $page->listRows;
            //print_r($sql);exit;
            $list =$this->user_model->query($sql);
            $this->assign('list', $list);
            $this->assign("page", $page->show('Admin'));
        }
        //$codes=$this->model->where(array('is_dis'=>0))->field('id,code,crtime,name,user_id')->order('id asc')->select();
        $this->assign("links", $codes);
        $this->display();
    }
    /*
     * 我的分销
     */
    public function links_my(){
        $type=I('request.id',1);
        $user_id=get_current_admin_id();
        if(get_groups_admin_user()==8 || get_groups_admin_user()==10 || get_groups_admin_user()==6 || get_groups_admin_user()==14 || $user_id==1){
            $where=array();
        }else{
            $where=array(array('user_id'=>$user_id));
        }
        $codes=$this->model->where($where)->where(array('is_dis'=>0))->field('id,code,crtime,name,user_id')->select();
        $code=array();
        $data=array();
        $i=1;
        foreach($codes as $v){
            if($type==$i){
                $code[]="'".$v['code']."'";
                $data[$v['id']]=$v['name'];
                $user_time=strtotime(date('Y-m-d',strtotime($v['crtime'])));
                break;
            }
            $i++;
        }
        $page=I('request.p',1);
        $num_per_page=I('get.num_per_page',10);
        if(!empty($code)){
//            $users_obj= M("Users");
//            $user=$users_obj->where(array('id'=>$user_id))->field('create_time')->find();
//            $user_time=strtotime($user['create_time']);
            $counts=ceil((time()-$user_time)/(24*60*60));//总数
            $endt=time()-$num_per_page*60*60*24*($page-1);
            $strt=$user_time>($endt-$num_per_page*60*60*24)?$user_time:($endt-$num_per_page*60*60*24);
            $page = $this->page($counts, $num_per_page);
            $where=array(
                'a.crtime >="'.date('Y-m-d 00:00:00',$strt).'"',
                'a.crtime <="'.date('Y-m-d 23:23:59',$endt).'"',
            );
            $dt=array();
            //总注册用户数
            $sql='SELECT COUNT(a.id) as total_num,DATE_FORMAT(a.crtime,"%Y-%m-%d") as thedate_time,b.id,c.amount  FROM system_user a LEFT JOIN system_distribution b on b.`code` = a.`code` LEFT JOIN (SELECT DATE_FORMAT(a.crtime, "%Y-%m-%d") AS crtime,SUM(b.amount) as amount FROM system_user a LEFT JOIN orders b ON b.`user_id` = a.`id` AND b.renew = 0 WHERE a.`code` IN ('.implode(',',$code).') and b.`status` in (1,2,3,4,8) GROUP BY crtime ) c on DATE_FORMAT(a.crtime, "%Y-%m-%d") = c.crtime where a.`code` in ('.implode(',',$code).') and '.implode(' and ',$where).' group by thedate_time';
//            if($user_id==1){
//                print_r($sql);exit;
//            }
            $count=$this->user_model->query($sql);
            $dt=check_array_links($count,$dt,'thedate_time');
            //实名认证用户数
            $count=$this->user_model->query('SELECT COUNT(a.id) as count_name,DATE_FORMAT(a.crtime,"%Y-%m-%d") as thedate_time,b.id  FROM system_user a LEFT JOIN system_distribution b on b.`code` = a.`code` where a.`code` in ('.implode(',',$code).') and a.type=5 and '.implode(' and ',$where).' group by thedate_time');
            $dt=check_array_links($count,$dt,'thedate_time');
            //申请借款用户数
            $count=$this->user_model->query('SELECT COUNT(a.id) as sq_user_num,DATE_FORMAT(a.crtime,"%Y-%m-%d") as thedate_time,b.id  FROM system_user a LEFT JOIN system_distribution b on b.`code` = a.`code` where a.`code` in ('.implode(',',$code).') and a.type=5 and a.type_borr>1 and '.implode(' and ',$where).' group by thedate_time');
            $dt=check_array_links($count,$dt,'thedate_time');
            //放款用户数
            $sql='SELECT COUNT(a.id) as fk_user_num,DATE_FORMAT(a.crtime,"%Y-%m-%d") as thedate_time,b.id FROM system_user a LEFT JOIN system_distribution b on b.`code` = a.`code` where a.`code` in ('.implode(',',$code).') and a.type>3 and a.is_renew=1 and '.implode(' and ',$where).' group by thedate_time';
            $count=$this->user_model->query($sql);
            $dt=check_array_links($count,$dt,'thedate_time');
            $dts=array();
            $num=($endt-$strt)/(24*60*60);
            for($i=0;$i<$num;$i++){
                $days=date('Y-m-d',strtotime('-'.$i.'days',$endt));
                if(empty($dt[$days])){
                    $dts[$days]=array();
                }else{
                    $dts[$days]=$dt[$days];
                }
            }
            $this->assign("links", $codes);
            $this->assign("links_name", $data);
            $this->assign("list", $dts);
            $this->assign("page", $page->show('Admin'));
        }
        $this->display();
    }
    /*
     * 分销统计
     */
    public function links_total()
    {
        $request = I( 'request.' );
        $times = 'a.crtime';
        $user_id = get_current_admin_id();
        $group=get_groups_admin_user();
        if($group==8 || $group==10 || $group==14 || $group==13 || $group==6 || $user_id==1 || $group==1) {
            if($id = I( 'request.id' )) {
                $where = [ 'id' => $id ];
            } else {
                $where = array();
            }
        } else {
            $where = array( array( 'user_id' => $user_id ) );
        }
        $links = $this->model->where( array( 'is_dis' => 0 ) )->field( 'id,name' )->select();
        $codes = $this->model->where( $where )->where( array( 'is_dis' => 0 ) )->field( 'id,code,crtime,name,user_id' )->select();//在用户分销配置表system_distribution中，查询非分销客户的信息（如邀请码等）
        foreach($codes as $v) {
            if(trim( $v['code'] ) != '') {
                $code []= "'" . $v['code'] . "'";//将非分销客户的邀请码拿到
                break;
            }
            //break;
        }

        if(!empty( $request['starts'] )) {
            $where[] = $times . ' >= "' . date( 'Y-m-d 00:00:00', strtotime( $request['starts'] ) ) . '"';
        }
        if(!empty( $request['ends'] )) {
            $where[] = $times . ' <= "' . date( 'Y-m-d 23:59:59', strtotime( $request['ends'] ) ) . '"';
        }
        if(empty( $request['ends'] ) && empty( $request['starts'] )) {
            $where[] = $times . ' >= "' . date( 'Y-m-d 00:00:00', strtotime( '-6days' ) ) . '"';
            $where[] = $times . ' <= "' . date( 'Y-m-d 23:59:59' ) . '"';
        }
        switch($request['timer_type']) {
            case '2'://周
                $select = "CONCAT(DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))+1 DAY),' ',DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))-5 DAY)) ";
                break;
            case '3'://月
                $select = " CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times})) ";
                break;
            default://日
                $select = " DATE_FORMAT({$times},'%Y-%m-%d') ";
                break;
        }
        $dt=[];
        if(!empty( $code )) {
            //$sql = 'SELECT COUNT(a.id) as total_num,' . $select . ' as thedate_time,b.id,c.amount  FROM system_user a LEFT JOIN system_distribution b on b.`code` = a.`code` LEFT JOIN (SELECT ' . $select . ' AS crtime,SUM(b.amount) as amount FROM system_user a LEFT JOIN orders b ON b.`user_id` = a.`id` AND b.renew = 0 WHERE a.`code` IN (' . implode( ',', $code ) . ') and b.`status` in (1,2,3,4,8) GROUP BY crtime ) c on ' . $select . ' = c.crtime where a.`code` in (' . implode( ',', $code ) . ') and ' . implode( ' and ', $where ) . ' group by thedate_time';
            $codeStr = implode( ',', $code );
            $otherWhere = implode( ' and ', $where );
            $otherWhere = $otherWhere ? ' and ' . $otherWhere : $otherWhere;
            $sql = "SELECT count(a.`id`) as total_num,{$select} AS thedate_time,SUM(b.amount) as amount FROM `system_user` a LEFT JOIN orders b ON b.`user_id` = a.`id` AND b.renew = 0 WHERE a.`code` IN ({$codeStr}) and b.`status` in (1,2,3,4,8) {$otherWhere} GROUP BY thedate_time";
            $count = $this->user_model->query( $sql );
            $dt=check_array_links($count,$dt,'thedate_time');
            $sql="SELECT COUNT(a.id) as zhu_total_num,{$select} as thedate_time,b.id  FROM system_user a LEFT JOIN system_distribution b on b.`code` = a.`code`  where a.`code` in (".implode(',',$code).") and ".implode(' and ',$where)." group by thedate_time";
            $count=$this->user_model->query($sql);
            $dt=check_array_links($count,$dt,'thedate_time');
        }
        //print_r($dt);exit;
        ksort($dt);
        $this->assign( "links", $links );
        $this->assign( "list", $dt );
        $this->display();
    }

    /*
    * 配置列表
    */
    public function links_list()
    {
        $request = I( 'request.' );
        $where = array(
            'is_dis' => array( 'eq', "0" ),
        );
        if(!empty( $request['name'] )) {
            $where['name'] = array( 'like', "%{$request['name']}%" );
        }
        $count = $this->model->where( $where )->count();
        $page = $this->page( $count, 20 );
        $list = $this->model
            ->where( $where )
            ->order( "crtime DESC" )
            ->limit( $page->firstRow . ',' . $page->listRows )
            ->select();
        $ids = array();
        foreach($list as $v) {
            if(empty( $v['user_id'] ))
                continue;
            $ids[] = $v['user_id'];
        }
        if(!empty( $ids )) {
            $user_list = array();
            $user = M( 'users' )->where( 'id in (' . implode( ',', $ids ) . ')' )->field( 'id,user_login' )->select();
            foreach($user as $v) {
                $user_list[$v['id']] = $v['user_login'];
            }
            $this->assign( 'list', $list );
            $this->assign( 'users', $user_list );
            $this->assign( "page", $page->show( 'Admin' ) );
        }
        $this->display();
    }

    /*
     * 添加
     */
    public function add()
    {
        $data = I( 'post.options' );
        if(!empty( $data )) {
            if(empty( $data['name'] )) {
                $this->error( '请输入终端名称！' );
            }
            if(empty( $data['code'] )) {
                $this->error( '请输入终端号码！' );
            }
            if(empty( $data['user'] )) {
                $this->error( '请选择关联用户' );
            }
            $url = C( 'JUMP_URL_OTHER' ) . "?app_act=links&merchantCode={$data['code']}&rcmdNetCode={$data['code']}";
//            $url = 'http://www.nuonuojinfu.cn/?app_act=links&merchantCode=rSfOc&rcmdNetCode=rSfOc';
//            $url = urlencode( $url );
//            $url = "http://api.t.sina.com.cn/short_url/shorten.json?source=2355515886&&url_long=" . $url;
            $shourt = $this->http_get( $url );
//            var_dump( $shourt );
            //exit;
            $dt = array(
                'code' => $data['code'],
                'name' => $data['name'],
                'user_id' => $data['user'],
                'short' => $shourt[0]['url_short'],
                //'short'=>'',
            );
            $co = $this->model->where( array( 'code' => $data['code'] ) )->find();
            if(empty( $co )) {
                $this->model->add( $dt );
                $datas = array(
                    'very_code' => md5( '!9*2S6a9$ss(s5apox' ),
                );
                $url = '?app_act=index/up_links';
                $this->http_post( C( 'JUMP_URL_OTHER' ) . $url, $datas );
                $this->success( "添加成功！", U( "indexlinks/links_list" ) );
            } else {
                $this->error( "终端号码重复请重新申请！" );
            }
        } else {
            //默认分销商的权限id是4
            $sql = 'select b.id,b.user_login from cmf_role_user a LEFT JOIN cmf_users b on a.user_id=b.id WHERE a.role_id=4';
            $users = M( 'users' )->query( $sql );
            $this->assign( 'list', $users );
            $this->display();
        }
    }

    /*
     * 删除
     */
    public function del()
    {
        $id = I( 'get.id' );
        if(empty( $id )) {
            $this->error( '数据传入失败！' );
        }
        $code = $this->model->where( array( 'id' => $id ) )->field( 'id' )->find();
        if(empty( $code )) {
            $this->error( '该分销不存在!,请刷新重试' );
        }
        $this->model->where( array( "id" => $id ) )->delete();
        $this->success( "删除成功！" );
    }

    function http_post($url, $param, $headers = null, $post_file = false)
    {
        $oCurl = curl_init();
        curl_setopt( $oCurl, CURLOPT_SAFE_UPLOAD, false );
        if(stripos( $url, "https://" ) !== false) {
            curl_setopt( $oCurl, CURLOPT_SSL_VERIFYPEER, false );
            curl_setopt( $oCurl, CURLOPT_SSL_VERIFYHOST, false );
            curl_setopt( $oCurl, CURLOPT_SSLVERSION, 1 ); //CURL_SSLVERSION_TLSv1
        }
        if(is_string( $param ) || $post_file) {
            $strPOST = $param;
        } else {
            $aPOST = array();
            foreach($param as $key => $val) {
                $aPOST[] = $key . "=" . urlencode( $val );
            }
            $strPOST = join( "&", $aPOST );
        }
        if($headers) {
            curl_setopt( $oCurl, CURLOPT_HTTPHEADER, $headers );
        }
        curl_setopt( $oCurl, CURLOPT_URL, $url );
        curl_setopt( $oCurl, CURLOPT_RETURNTRANSFER, 1 );
        curl_setopt( $oCurl, CURLOPT_POST, true );
        curl_setopt( $oCurl, CURLOPT_POSTFIELDS, $strPOST );
        $sContent = curl_exec( $oCurl );
        $aStatus = curl_getinfo( $oCurl );
        curl_close( $oCurl );
        if(intval( $aStatus["http_code"] ) == 200) {
            $sContent = json_decode( $sContent, true );
            return $sContent;
        } else {
            return false;
        }
    }

    function http_get($url, $headers = null)
    {
        $oCurl = curl_init();
        if(stripos( $url, "https://" ) !== false) {
            curl_setopt( $oCurl, CURLOPT_SSL_VERIFYPEER, false );
            curl_setopt( $oCurl, CURLOPT_SSL_VERIFYHOST, false );
            curl_setopt( $oCurl, CURLOPT_SSLVERSION, 1 ); //CURL_SSLVERSION_TLSv1
        }
        if($headers) {
            curl_setopt( $oCurl, CURLOPT_HTTPHEADER, $headers );
        }
        curl_setopt( $oCurl, CURLOPT_URL, $url );
        curl_setopt( $oCurl, CURLOPT_RETURNTRANSFER, 1 );
        $sContent = curl_exec( $oCurl );
        $aStatus = curl_getinfo( $oCurl );
        curl_close( $oCurl );
        if(intval( $aStatus["http_code"] ) == 200) {
            $sContent = json_decode( $sContent, true );
            return $sContent;
        } else {
            return false;
        }
    }

    /*
     * 导入
     */
    function daoru_tow()
    {
        $this->daoru( 2 );
    }

    function daoru_third()
    {
        $this->daoru( 3 );
    }
    function daoru_third_one()
    {
        $this->daoru( 4 );
    }
    //导入其他类型
    function daoru_otherone()
    {
        $this->daoru( 11 );
    }
    function daoru_othertwo()
    {
        $this->daoru( 12 );
    }
    function daoru($ty = 1)
    {
        if(empty( $_FILES )) {
            $ty = I( 'get.ty', $ty );
            $where = array(
                'type=' . $ty,
            );
            $request = I( 'request.' );
            if(!empty( $request['name'] )) {
                $where[] = 'name like "%' . $request['name'] . '%"';
            }
            if(!empty( $request['mobile'] )) {
                $where[] = 'mobile like "%' . $request['mobile'] . '%"';
            }
            if(!empty( $request['starts'] )) {
                $where[] = 'uptime >= "' . date('Y-m-d 00:00:00',strtotime($request['starts']))  . '"';
            }
            if(!empty( $request['ends'] )) {
                $where[] = 'uptime <= "' . date('Y-m-d 23:59:59',strtotime($request['ends']))  . '"';
            }
            if(!empty( $request['message'] )) {
                if($request['message']=='空值'){
                    $where[] = '(message="" or message is null)';
                }else{
                    $where[] = 'message like "%' . $request['message'] . '%"';
                }
            }
            $count = M( "other", 'mobile_', C( 'DB_CONFIG2' ) )->where( implode( ' and ', $where ) )->count();
            $page = $this->page( $count, 20 );
            $list = M( "other", 'mobile_', C( 'DB_CONFIG2' ) )->where( implode( ' and ', $where ) )
                ->field('mobile,name,sex,identity,uptime,message,qqs,weixin,application_time,school,school_province,school_city,borrow_amount,email')
                ->order( "uptime DESC" )
                ->limit( $page->firstRow . ',' . $page->listRows )
                ->select();
            $this->assign( 'ty', $ty );
            $this->assign( 'list', $list );
            $this->assign( "page", $page->show( 'Admin' ) );
            $this->display( 'daoru' );
        } else {
            set_time_limit( 120 );
            ini_set( 'memory_limit', '1024M' );
            header( "Content-type: text/html; charset=utf-8" );
            $path = '/data/upload/xml/';
            $upload = new \Think\Upload();// 实例化上传类
            $upload->maxSize = 3145728;// 设置附件上传大小
            $upload->exts = array( 'xls', 'csv', 'xlsx' );// 设置附件上传类型
            $upload->rootPath = '.' . $path; // 设置附件上传根目录
            $upload->savePath = ''; // 设置附件上传（子）目录
            // 上传文件
            $info = $upload->upload();
            if(empty( $info )) {// 上传错误提示错误信息
                $this->error( $upload->getError() );
            }
            $url = "http://127.0.0.1/?app_act=index/daoru";
            $post_data = array(
                'sign' => md5( '导入' . md5( 'nuonuojinfu321' ) ),
                'address' => dirname( dirname( dirname( dirname( __FILE__ ) ) ) ) . $path . $info['file']['savepath'] . $info['file']['savename'],
            );
            $data = $this->http_post( $url, $post_data );
            if(!empty( $data['code'] ) || empty( $data ) || $data['fh'] == 5) {
                $this->error( '上传失败！' );
            }
            $times = date( 'Y-m-d H:i:s' );
            $i = 0;
            $val = array();
            $type = I( 'get.ty', 1 );
            foreach($data['datas'] as $k => $v) {
                if($k == 0 || empty( $v[0] )) {
                    continue;
                }
                if($type>10){
                    $vals = array(
                        '"' . $v[0] . '"',
                        '"' . $v[1] . '"',
                        '"' . $v[2] . '"',
                        '"' . $v[3] . '"',
                        '"' . $this->excelTime($v[4]) . '"',
                        '"' . $v[5] . '"',
                        '"' . $v[6] . '"',
                        '"' . $v[7] . '"',
                        '"' . $v[8] . '"',
                        '"' . $times . '"',
                        '"' . $type . '"',
                    );
                }else{
                    $vals = array(
                        '"' . $v[0] . '"',
                        '"' . $v[1] . '"',
                        '"' . $v[2] . '"',
                        '"' . $v[3] . '"',
                        '"' . $v[4] . '"',
                        '"' . $v[5] . '"',
                        '"' . $times . '"',
                        '"' . $type . '"',
                    );
                }
                $val[$i][] = '(' . implode( ',', $vals ) . ')';
                if(count( $val[$i] ) == 80) {
                    $i++;
                }
            }
            foreach($val as $v) {
                if($type>10){
                    $sql = "insert into mobile_other(`name`,`mobile`,`identity`,`email`,`application_time`,`school`,`school_province`,`school_city`,`borrow_amount`,`uptime`,`type`) value " . implode( ',', $v ) . " ON duplicate KEY UPDATE uptime=VALUES(uptime);";
                }else{
                    $sql = "insert into mobile_other(`name`,`sex`,identity,mobile,qqs,weixin,uptime,`type`) value " . implode( ',', $v ) . " ON duplicate KEY UPDATE uptime=VALUES(uptime);";
                }
                //M("other",'mobile_',C('DB_CONFIG2'))->execute($sql);
                M( "other", 'mobile_', C( 'DB_CONFIG2' ) )->execute( $sql );
            }
            $this->success( "导入成功！", U( "indexlinks/daoru" ) );
        }
    }
    function daochu(){
        set_time_limit(120) ;
        ini_set( 'memory_limit', '2048M' );
        $ty = I( 'get.ty', 1 );
        $where = array(
            'type=' . $ty,
        );
        $request = I( 'request.' );
        if(!empty( $request['name'] )) {
            $where[] = 'name like "%' . $request['name'] . '%"';
        }
        if(!empty( $request['mobile'] )) {
            $where[] = 'mobile like "%' . $request['mobile'] . '%"';
        }
        if(!empty( $request['starts'] )) {
            $where[] = 'uptime >= "' . date('Y-m-d 00:00:00',strtotime($request['starts']))  . '"';
        }
        if(!empty( $request['ends'] )) {
            $where[] = 'uptime <= "' . date('Y-m-d 23:59:59',strtotime($request['ends']))  . '"';
        }
        if(!empty( $request['message'] )) {
            if($request['message']=='空值'){
                $where[] = '(message="" or message is null)';
            }else{
                $where[] = 'message like "%' . $request['message'] . '%"';
            }
        }
        $count = M( "other", 'mobile_', C( 'DB_CONFIG2' ) )->where( implode( ' and ', $where ) )->count();
        $mulit_arr = array(
            array("手机号","姓名",'性别','身份证','上传时间','审核备注','QQ','微信','申请时间','学校名称','学校省份','学校城市','额度','邮箱'),
        );
        //列数,需要时再添加,避免多次循环
        $cell_title_arr = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N'];
        $width_arr = [20,10,15,15,30,15,20,15,15,25];
        /* @实例化 */
        vendor('PHPExcel.PHPExcel');
        $obpe = new \PHPExcel();
        $step = 6000;
        $j=0;
        for ($i=0; $i<$count; $i=$i+$step) {
            $list = M( "other", 'mobile_', C( 'DB_CONFIG2' ) )->where( implode( ' and ', $where ) )
                ->field('mobile,name,sex,identity,uptime,message,qqs,weixin,application_time,school,school_province,school_city,borrow_amount,email')
                ->order( "uptime DESC" )
                ->limit( $i . ',' . $step )
                ->select();
            $obpe->setActiveSheetIndex($j);
            $obpe->getActiveSheet()->setTitle("Worksheet".$j);
            $obpe->setactivesheetindex($j);
            $j++;
            //写入多行数据
            array_unshift($list,$mulit_arr);
            foreach($list as $k=>$v){
                $k = $k+1;
                if($k==1){
                    /* @func 设置列-表头 */
                    foreach($cell_title_arr AS $ckey=>$cval){
                        $obpe->getactivesheet()->setcellvalue($cval.$k, $v[0][$ckey]);
                    }
                }else{
                    /* @func 设置列 */
                    $obpe->getactivesheet()->setcellvalue('A'.$k, $v['mobile']);
                    $obpe->getactivesheet()->setcellvalue('B'.$k, $v['name']);
                    $obpe->getactivesheet()->setcellvalue('C'.$k, $v['sex']);
                    $obpe->getactivesheet()->setcellvalue('D'.$k, "'".$v['identity']);
                    $obpe->getactivesheet()->setcellvalue('E'.$k, $v['uptime']);
                    $obpe->getactivesheet()->setcellvalue('F'.$k, $v['message']);
                    $obpe->getactivesheet()->setcellvalue('G'.$k, $v['qqs']);
                    $obpe->getactivesheet()->setcellvalue('H'.$k, $v['weixin']);
                    $obpe->getactivesheet()->setcellvalue('I'.$k, $v['application_time']);
                    $obpe->getactivesheet()->setcellvalue('J'.$k, $v['school']);
                    $obpe->getactivesheet()->setcellvalue('K'.$k, $v['school_province']);
                    $obpe->getactivesheet()->setcellvalue('L'.$k, $v['school_city']);
                    $obpe->getactivesheet()->setcellvalue('M'.$k, $v['borrow_amount']);
                    $obpe->getactivesheet()->setcellvalue('N'.$k, $v['email']);
                }
            }
            /* 设置宽度 */
            foreach($cell_title_arr AS $ckey=>$cval){
                $obpe->getActiveSheet()->getColumnDimension($cval)->setWidth(20);
            }
            $obpe->createSheet();
        }

        $obpe->setactivesheetindex(0);
        //写入类容
        $obwrite = \PHPExcel_IOFactory::createWriter($obpe, 'Excel5');
        header('Pragma: public');
        header('Expires: 0');
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Content-Type:application/force-download');
        header('Content-Type:application/vnd.ms-execl');
        header('Content-Type:application/octet-stream');
        header('Content-Type:application/download');
        header("Content-Disposition:attachment;filename='回访记录".$ty.".xls'");
        header('Content-Transfer-Encoding:binary');
        $obwrite->save('php://output');
    }
    function daoru_info()
    {
        $mobile = I( 'request.mobile', 0 );
        if(empty( $mobile )) {
            $this->error( '数据传入失败！' );
        }
        $list = M( "other", 'mobile_', C( 'DB_CONFIG2' ) )->where( array( 'mobile' => $mobile ) )->find();
        $url = $_SERVER['HTTP_REFERER'];
        if(strstr( $url, 'daoru_info' )) {
            $url = session( 'daoru_return' );
        } else {
            session( 'daoru_return', $url );
        }
        $this->assign( 'ty', I( 'request.ty', 1) );
        $this->assign( 'list', $list );
        $this->assign( 'url', $url );
        $this->display();
    }

    function daoru_act()
    {
        $data = I( 'post.options' );
        if(empty( $data['mobile'] )) {
            $this->error( '数据传入失败！' );
        }
        $dt = array(
            'mobile' => $data['mobile'],
            'message' => $data['message'],
        );
        M( "other", 'mobile_', C( 'DB_CONFIG2' ) )->where( array( 'mobile' => $data['mobile'] ) )->save( $dt );
        $this->success( "审核成功！", $data['url'] );
    }
    /*
     * 启用关闭
     */
    function is_ban(){
        $id = I( 'get.id', 0);
        if(!empty($id)) {
            $type = I( 'get.type', 0);
            $result = M( "distribution", 'system_', C( 'DB_CONFIG2' ) )
                ->where( [ "id" => $id ] )->setField( 'is_start', $type );
            if($result) {
                $this->success( "启用成功！");
            } else {
                $this->error( '启用失败！' );
            }
        } else {
            $this->error( '数据传入失败！' );
        }
    }
    /*
     * excel时间转化
     */
    function excelTime($date) {
        if (!is_numeric( $date )) {
            $date=date('Ymd',strtotime($date));
            return $date;
        }
        if(!function_exists('GregorianToJD')){
            //if (is_numeric( $date )) {
            $is_time=$date;
            $jd = GregorianToJD( 1, 1, 1970 );
            $gregorian = JDToGregorian( $jd + intval ( $date ) - 25569 );
            $date = explode( '/', $gregorian );
            $date_str = str_pad( $date [2], 4, '0', STR_PAD_LEFT )
                . str_pad( $date [0], 2, '0', STR_PAD_LEFT )
                . str_pad( $date [1], 2, '0', STR_PAD_LEFT );
            $date=date('Y-m-d H:i:s', strtotime($date_str));
            //}
        }else{
            //            $date=$date>25568?$date+1:25569;
            //            /*There was a bug if Converting date before 1-1-1970 (tstamp 0)*/
            //            $ofs=(70 * 365 + 17+2) * 86400;
            //            $date =  date("Ymd",($date * 86400) - $ofs);
            $t = $date; //读取到的值
            $n = intval(($t - 25569) * 3600 * 24); //转换成1970年以来的秒数
            $date=gmdate('Y-m-d H:i:s', $n);//格式化时间
        }
        return $date;
    }
}
